<?php

class multitender_model_analytics extends multitender_model {

    function  __construct() {
        parent::__construct();
        $this->db_person = & $this->conf['dbs']['person'];
        $this->db_tenders = & $this->conf['dbs']['tenders'];
    }

    function getPriceDrop ($reg) {
        $this->db_tenders->SetFetchMode(ADODB_FETCH_ASSOC);
        $sql = "SELECT id, (price - price_best) *100 / price AS ddrop, type_id, rubric_id
                FROM item
                WHERE region_id =$reg AND price_best>0
                    AND price>price_best AND price<4294967295
                ORDER BY ddrop DESC LIMIT 10000";
    
    $result = $this->db_tenders->GetArray($sql);
    return $result;
    }
//информация и графики по поставщикам. на регионах с большим кол-вом поставщиков вылетает. запросы оптимизированы
    /*function get_count_of_contracts($region_id) {
        $sql = "SELECT id
                FROM distributor
                WHERE region_id =$region_id";

        $result = $this->db_tenders->GetAll($sql);
        $list = array();
        foreach ($result as $ids) {
            $list[] = $ids['id'];
        }
        $otvet['distributor_count'] = count($list);
        
        $sql = "SELECT id_contract FROM link_contracts_with_distributors WHERE id_distributor IN (".implode($list, ",").")";
        $result = $this->db_tenders->GetAll($sql);
        $list = array();
        foreach ($result as $ids) {
            $list[] = $ids['id_contract'];
        }
        $otvet['count']=count($list);
        $sql = "SELECT SUM(contract_price) AS summa, YEAR(contract_date) AS year_gr FROM contract WHERE id IN (".implode($list, ",").") GROUP BY year_gr";
        $result = $this->db_tenders->GetAll($sql);
        $otvet['graph']=$result;
        return $otvet;
    }*/

    function getTopDistributors($reg) {
        $sql = "SELECT distributor.id AS dist_id, SUM(contract.contract_price) AS summa, COUNT(contract.id) AS cnt FROM distributor INNER JOIN link_contracts_with_distributors ON distributor.id = link_contracts_with_distributors.id_distributor INNER JOIN contract ON contract.id = link_contracts_with_distributors.id_contract WHERE distributor.region_id = ? GROUP BY distributor.id ORDER BY summa DESC LIMIT 5";
        $items = $this->db->GetAll($sql, array($reg));
        
        $mas=array();
        $i=0;
        foreach ($items as $ids) {
            $mas[$i] = $ids['dist_id'];
            $i++;
        }
        $sql = "SELECT id, Name FROM distributor WHERE id IN (".implode($mas, ",").")";
        $names = $this->db->GetAll($sql);
        foreach ($names as $nm) {
            foreach ($items as &$result) {
                if ($result['dist_id'] == $nm['id']) {
                    $result['Name'] = $nm['Name'];
                    $result['summa'] = round($result['summa']/1000000);
                }
            }
        }

        return $items;
    }

    function getBiddersCount ($reg) {
        $sql = "SELECT id, members_count, type_id
                FROM item
                WHERE region_id =$reg AND members_count IS NOT NULL
                ORDER BY members_count DESC LIMIT 10000";

    $result = $this->db_tenders->GetArray($sql);
    return $result;
    }

    function getRegionName($reg) {
        $sql = "SELECT name, name_rp FROM region WHERE id=$reg";
        return $this->db_tenders->GetRow($sql);
    }

    function getCustomersCount($reg) {
        $sql = "SELECT COUNT(*) FROM customer WHERE region_id=$reg";
        return $this->db_tenders->GetOne($sql);
    }

    function getTendersCount($reg) {
        $sql = "SELECT COUNT(*) FROM item WHERE region_id=$reg";
        return $this->db_tenders->GetOne($sql);
    }
    function getTendersSum($reg) {
        $sql = "SELECT SUM(price) FROM item WHERE region_id=$reg";
        return $this->db_tenders->GetOne($sql);
    }
    function getTopExpensive ($reg) {
        $sql = "SELECT id,name,price FROM item WHERE region_id=$reg AND price<>2147483647 AND price<>4294967295 ORDER BY price DESC LIMIT 5";
        return $this->db_tenders->GetArray($sql);
    }

    function getCustomersNames($ids) {
        if (!is_array($ids)) {
            return array();
        }
        $sql = "SELECT id, name FROM customer WHERE id IN (".implode(', ', $ids).")";
        $customers = $this->db->GetAll($sql);
        return $customers;
    }

    function getTopCustomers($reg) {
        $sql = "SELECT COUNT(*) as count, SUM(price) as sum, customer_id FROM item WHERE region_id=$reg GROUP BY customer_id HAVING customer_id IS NOT NULL ORDER BY sum DESC LIMIT 5";
        $items = $this->db->GetAll($sql);
        return $items;
    }

    function getDropByRubric($reg) {
        $sql = "SELECT rubric_id, AVG(ddrop) as ddrop FROM ( SELECT rubric_id, (price - price_best) *100 / price AS ddrop
                FROM item
                WHERE region_id=? AND price>price_best
                AND rubric_id<>0 ) as smth
                GROUP BY rubric_id
                HAVING ddrop BETWEEN 2 AND 98";
        return $this->db_tenders->GetAll($sql, array($reg));
    }

    function getCountsByType($reg) {
        $sql = 'SELECT type_id, COUNT(*) as cnt FROM item WHERE region_id=? AND type_id IN (1,2,3,6) GROUP BY item.type_id';
        return $this->db_tenders->GetAll($sql, array($reg));
    }
    function getItemName($id) {
        $sql = "SELECT name FROM item WHERE id=$id";
        return $this->db_tenders->GetOne($sql);
    }


}